47
How can I prevent grouping by a specified column
With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.DataColumns.Item("Freight").AllowGroupBy = 0
End With
46
How can I sort alphabetically the columns to be displayed in the context menu/floating panel

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotBarVisible = 69595 ' PivotBarVisibleEnum.exPivotBarContextSortAscending Or PivotBarVisibleEnum.exPivotBarAllowResizeColumns Or PivotBarVisibleEnum.exPivotBarAllowUndoRedo Or PivotBarVisibleEnum.exPivotBarAutoUpdate Or PivotBarVisibleEnum.exPivotBarAllowFormatContent Or PivotBarVisibleEnum.exPivotBarAllowFormatAppearance Or PivotBarVisibleEnum.exPivotBarAllowValues Or PivotBarVisibleEnum.exPivotBarShowTotals Or PivotBarVisibleEnum.exPivotBarAutoFit Or PivotBarVisibleEnum.exPivotBarSizable Or PivotBarVisibleEnum.exPivotBarVisible
	.PivotColumnsSortOrder = 1
	.PivotColumnsFloatBarVisible = True
End With
45
How can I prevent dropping data to the control
With Pivot1
	.AllowDrop = False
End With
44
Is it possible to allow incremental filtering on drop down filter window too, as I can on the control menus

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0"
	.DisplayFilterList = 42272 ' FilterListEnum.exHideFilterPattern Or FilterListEnum.exFilterListDefault
End With
43
How can I prevent showing the Filter For field in the drop down filter window

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0"
	.DisplayFilterList = 42272 ' FilterListEnum.exHideFilterPattern Or FilterListEnum.exFilterListDefault
End With
42
How can I display the numeric columns only when selecting a new aggregate, like SUM

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0[bold]"
	.PivotColumns = "sum(5)/12,count(5)/12"
	.DataColumns.Item("Freight").SortType = 1
End With
41
How can I add a value column

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0"
	.PivotColumns = "sum(5)/12"
End With
40
I would like to always have the subtotals in the same row of the "father row". Could that be done

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0[bold],2"
	.PivotColumns = "sum(5)/12,count(5)/12"
	.ShowViewCompact = 35 ' ShowViewCompactEnum.exViewCompactAggregates Or ShowViewCompactEnum.exViewCompact
	.PivotTotals = "/sum,sum(0)"
End With
39
Is there any way, when I change the filter of the column, it broadcast the filter to the other pivot columns that were duplicated

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0,2"
	.ShowBranchRows = 2
	.PivotColumns = "sum(5)/12[filter='gBpNxjNh1MhlBoKNhpOZ0hJVNxpOhlMggKBhMZrMJnMoAgI='],count(5)/12[filter='gBpNxjNh1MhlBoKNhpOZ0hJVNxpOhlMggKBhMZ" & _
"rMJnMoAgI=']"
	.ShowViewCompact = 19 ' ShowViewCompactEnum.exViewCompactKeepSettings Or ShowViewCompactEnum.exViewCompact
End With
38
How can I summarize more fields in the same cell

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0,2"
	.ShowBranchRows = 2
	.PivotColumns = "sum(5)/12,count(5)/12"
	.ShowViewCompact = 3
End With
37
How do I programmatically group by rows, in a compact way, no hierarchy lines

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0,2"
	.ShowBranchRows = 2
End With
36
How do I programmatically group by rows

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0,1,2"
	.LinesAtRoot = -1
	.HasLines = 1
End With
35
How do I programmatically group by columns

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0"
	.PivotColumns = "sum(5)/12;6"
End With
34
How can I hide the add new button on the pivot bar

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotBarVisible = 8155 ' PivotBarVisibleEnum.exPivotBarHideAddNew Or PivotBarVisibleEnum.exPivotBarAllowResizeColumns Or PivotBarVisibleEnum.exPivotBarAllowUndoRedo Or PivotBarVisibleEnum.exPivotBarAutoUpdate Or PivotBarVisibleEnum.exPivotBarAllowFormatContent Or PivotBarVisibleEnum.exPivotBarAllowFormatAppearance Or PivotBarVisibleEnum.exPivotBarAllowValues Or PivotBarVisibleEnum.exPivotBarShowTotals Or PivotBarVisibleEnum.exPivotBarAutoFit Or PivotBarVisibleEnum.exPivotBarSizable Or PivotBarVisibleEnum.exPivotBarVisible
End With
33
Is it possible to show the data that generated the result, when double clicking the row

With Pivot1
	.BeginUpdate 
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0[italic]"
	.PivotColumns = "count(0)[underline]"
	.PivotTotals = "count[bold,strikeout]"
	.ShowDataOnDblClick = True
	.EndUpdate 
End With
32
Does your control support subscript or superscript, in HTML captions

With Pivot1
	.HeaderHeight = 22
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.DataColumns.Item("ShipCountry").Caption = "ShipCountry<font ;7><off 6><sha ;;0>subscript"
	.DataColumns.Item("ShipRegion").Caption = "ShipRegion<font ;7><off -6><sha ;;0>superscript"
	.Refresh 
End With
31
Is it possible to define a different background color for the pivot bar

With Pivot1
	.Background(97) = RGB(240,240,240)
End With
30
How can I display an icon/image to Content sub-menu

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.Images "gBJJgBAIDAAGAAEAAQhYAf8Pf4hh0QihCJo2AEZjQAjEZFEaIEaEEaAIAkcbk0olUrlktl0vmExmUzmk1m03nE5nU7nk9n0/oFBoVDolFo1HpFJpVLplNp1PqFRqVTq" & _
"lVq1XrFZrVbrldr1fsFhsVjslls1ntFptVrtltt1vuFxuVzul1u13vF5vV7vl9v1/wGBwWDwmFw2HxGJxWLxmNx0xiFdyOTh8Tf9ZymXx+QytcyNgz8r0OblWjyWds+m" & _
"0ka1Vf1ta1+r1mos2xrG2xeZ0+a0W0qOx3GO4NV3WeyvD2XJ5XL5nN51aiw+lfSj0gkUkAEllHanHI5j/cHg8EZf7w8vl8j4f/qfEZeB09/vjLAB30+kZQAP/P5/H6/y" & _
"NAOAEAwCjMBwFAEDwJBMDwLBYAP2/8Hv8/gAGAD8LQs9w/nhDY/oygIA="
	.FormatContents.Item("numeric").Name = "<img>1</img> Numeric"
	.PivotRows = "5[content=numeric]"
End With
29
How can I change the selection background in the control's context menu

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.Background(20) = RGB(255,0,0)
End With
28
How can I display the column as date in a long format

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.FormatContents.Add "longdate","longdate(date(value))"
	.PivotRows = "9[content=longdate]"
End With
27
Is it possible to display the column in upper-case

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.FormatContents.Add "upper","upper(value)"
	.PivotRows = "0[content=upper]"
End With
26
How can I programatically bold a column

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0[bold]"
End With
25
How can I display the total with a different foreground color

With Pivot1
	.FormatAppearances.Add("fore").ForeColor = RGB(255,0,0)
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0"
	.PivotColumns = "sum(5)"
	.PivotTotals = "sum[fore,bold]"
End With
24
How can I display the total with a different background color/ebn

With Pivot1
	.VisualAppearance.Add 1,"c:\exontrol\images\normal.ebn"
	.FormatAppearances.Add("back").BackColor = &H1000000
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0"
	.PivotColumns = "sum(5)"
	.PivotTotals = "sum[back]"
End With
23
How can I display the total with a solid background color

With Pivot1
	.FormatAppearances.Add("back").BackColor = RGB(240,240,240)
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0"
	.PivotColumns = "sum(5)"
	.PivotTotals = "sum[back]"
End With
22
Is it possible to change the "bold" caption in the control's context menu

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.FormatAppearances.Item("bold").Name = "Ingrosat"
End With
21
Is it possible to show no Exclude field in the filter window

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.DisplayFilterList = 288 ' FilterListEnum.exShowCheckBox Or FilterListEnum.exSortItemsAsc
End With
20
How can I prevent showing the drop down filter button

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.DisplayFilterList = 2
End With
19
How do I get the count of positive values only

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.Aggregates.Add("positive","sum").FormatValue = "value < 0 ? 0 : 1"
	.PivotRows = "0"
	.PivotColumns = "positive(5)"
End With
18
How do I get the sum for negative values only

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.Aggregates.Add("negative","sum").FormatValue = "value < 0 ? value : 0"
	.PivotRows = "0"
	.PivotColumns = "negative(5)"
End With
17
My data stores the data as strings, is it possible to load the data using Import method

With Pivot1
	.Import "'string 1';'string 2'#'string 3';'string 4'","str=`'` eor='#' eof=';' hdr=0"
End With
16
Is it possible to load data using different separators

With Pivot1
	.Import "item 1;item 2#item 3;item 4","eor='#' eof=';' hdr=0"
End With
15
Is it possible to align a column

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	With .DataColumns.Item(0)
		.Alignment = 2
		.HeaderAlignment = 2
	End With
	.Refresh 
End With
14
How can I change by code the column/rows background color

With Pivot1
	.BeginUpdate 
	.VisualAppearance.Add 1,"c:\exontrol\images\normal.ebn"
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.FormatAppearances.Add("aka").BackColor = &H1000000
	.PivotRows = "0[aka]"
	.EndUpdate 
End With
13
How can I apply by code any appearance to my list

With Pivot1
	.BeginUpdate 
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0[italic]"
	.PivotColumns = "count(0)[underline]"
	.PivotTotals = "count[bold,strikeout]"
	.EndUpdate 
End With
12
How can I display an icon instead SUM/Total field

With Pivot1
	.BeginUpdate 
	.Images "gBJJgBggAAwAAgACEKAD/hz/EMNh8TIRNGwAjEZAEXjAojJAjIgjIBAEijUlk8plUrlktl0vmExmUzmk1m03nE5nU7nk9n0/oFBoVDolFo1HpFJpVLplNp1PqFRqVTq" & _
"lVq1XrFZrVbrldr1fsFhsVjslls1ntFptVrtltt1vuFxuVzul1u13vF5vV7vl9oEEwGBwWDwmFw2Hw9+xUsxGNx2Px+LyUnyGVy2VyeZAGNjIJjITjIb0OjjGi0ukAAV" & _
"jILzmayWtAGejCvjLh2u3jG23O4ACx1ew11+zEYGsZZsZUe/wkZ4sYZvD4PCy8kjAzjLFjKd5WDjIz6HRvnTwUZGMZX8ZTPb8XU8Hh9cFjALjKVjK5jIv9/w9t78WdjJ" & _
"IoyWr7sKjIWu+/a8Og2QAEajLaIxAzlwhB0DwQuzoECjJWw1DiMQ3D0OgAQMKwsuj8xOy0SrzFEWMdFUExbGMCRfC8ZRswMaLsiofJVHiOo+kKRs2lL2Jsh8cyQo6Ag=" & _
"="
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	With .Aggregates.Item("sum")
		.Name = "<img>1</img> Sum"
		.Caption = "<img>1</img>"
	End With
	.PivotColumnsFloatBarVisible = True
	.FormatPivotHeader = "iaggregate ? (caggregate + (iaggregate != 5 ? ' ' : '') + caption) : caption"
	.FormatPivotTotal = "caggregate"
	.PivotRows = "0"
	.PivotColumns = "sum(5)"
	.PivotTotals = "sum,count"
	.EndUpdate 
End With
11
How can I change the caption to be displayed when dragging an aggregate function

With Pivot1
	.BeginUpdate 
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotColumnsFloatBarVisible = True
	.FormatPivotHeader = "(iaggregate ? ('<b>' + upper(caggregate) + '</b> of ' + caption) : caption)"
	.PivotRows = "0"
	.PivotColumns = "sum(5)"
	.PivotTotals = "sum,count"
	.EndUpdate 
End With
10
I am using Import method, just wondering if I can rename the columns

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	With .DataColumns.Item(0)
		.Caption = "New Caption"
		.PivotCaption = "New Pivot Caption"
	End With
	.Refresh 
End With
9
Does your control support Fit-To-Page Print and Print Preview

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0,1,2"
	.ExpandAll 
	With CreateObject("Exontrol.Print")
		.Options = "FitToPage = On"
		.PrintExts = Pivot1
		.Preview 
	End With
End With
8
How can I print the control

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0,1,2"
	.ExpandAll 
	With CreateObject("Exontrol.Print")
		.PrintExts = Pivot1
		.Preview 
	End With
End With
7
How can I hide the pivot bar (hide completly)

With Pivot1
	.PivotBarVisible = 4058 ' PivotBarVisibleEnum.exPivotBarAllowResizeColumns Or PivotBarVisibleEnum.exPivotBarAllowUndoRedo Or PivotBarVisibleEnum.exPivotBarAutoUpdate Or PivotBarVisibleEnum.exPivotBarAllowFormatContent Or PivotBarVisibleEnum.exPivotBarAllowFormatAppearance Or PivotBarVisibleEnum.exPivotBarAllowValues Or PivotBarVisibleEnum.exPivotBarShowTotals Or PivotBarVisibleEnum.exPivotBarAutoFit Or PivotBarVisibleEnum.exPivotBarSizable
End With
6
How can I hide the pivot bar (auto-hide)

With Pivot1
	.PivotBarVisible = 4083 ' PivotBarVisibleEnum.exPivotBarAllowResizeColumns Or PivotBarVisibleEnum.exPivotBarAllowUndoRedo Or PivotBarVisibleEnum.exPivotBarAutoUpdate Or PivotBarVisibleEnum.exPivotBarAllowFormatContent Or PivotBarVisibleEnum.exPivotBarAllowFormatAppearance Or PivotBarVisibleEnum.exPivotBarAllowValues Or PivotBarVisibleEnum.exPivotBarAutoHide Or PivotBarVisibleEnum.exPivotBarShowTotals Or PivotBarVisibleEnum.exPivotBarSizable Or PivotBarVisibleEnum.exPivotBarVisible
End With
5
How can I count and get the total of a specified column

With Pivot1
	.BeginUpdate 
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotColumnsFloatBarVisible = True
	.PivotRows = "0"
	.PivotColumns = "sum(5)"
	.PivotTotals = "sum,count"
	.EndUpdate 
End With
4
How can I add show the columns once I grouped a column

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0"
	.PivotColumnsFloatBarVisible = True
End With
3
How can I programatically group the columns

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
	.PivotRows = "0:D"
End With
2
Is it possible to load data from a data source

With Pivot1
	Set rs = CreateObject("ADOR.Recordset")
	With rs
		.Open "Data","Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\Exontrol\ExPivot\Sample\Access\sample.accdb",3,3
	End With
	.DataSource = rs
End With
1
How can I load data

With Pivot1
	.Import "C:\Program Files\Exontrol\ExPivot\Sample\data.txt"
End With